package com.compomics.prioritizer.db.accessors;

import java.util.HashMap;
import java.util.Vector;
import java.sql.*;

/**
 * Created by IntelliJ IDEA.
 * User: Niklaas Colaert
 * Date: 31-okt-2008
 * Time: 9:05:00
 * To change this template use File | Settings | File Templates.
 */
public class Project extends ProjectTableAccessor {

    /**
     * Default constructor.
     */
    public Project() {
        super();
    }

    /**
     * Wrapper for the superclass constructor.
     *
     * @param aParams   HashMap with the parameters.
     */
    public Project(HashMap aParams) {
        super(aParams);
    }

    /**
     * This constructor reads the project from a resultset. The ResultSet should be positioned such that
     * a single row can be read directly (i.e., without calling the 'next()' method on the ResultSet). <br />
     * The columns should be in this order: <br />
     * Column 1: project ID <br />
     * Column 2: name of the project <br />
     * Column 3: the description for the project <br />
     * Column 4: the operator <br/>
     * Column 4: username <br />
     * Column 5: creationdate <br />
     * Column 6: modificationdate.
     *
     * @param   aRS ResultSet to read the data from.
     * @exception java.sql.SQLException    when reading the ResultSet failed.
     */
    public Project(ResultSet aRS) throws SQLException {
        /*iProjectid = aRS.getLong(1);
        iName = aRS.getString(2);
        iDescription = aRS.getString(3);
        iL_userid = aRS.getLong(4);
        iL_labelid = aRS.getLong(5);
        iL_taxonomyid = aRS.getLong(6);
        iL_protocolid = aRS.getLong(7);
        iL_instrumentid_proposed = aRS.getLong(8);
        iL_instrumentid_accepted = aRS.getLong(9);
        iPriority = aRS.getLong(10);
        iUsername = aRS.getString(11);
        iCreationdate = (java.sql.Timestamp)aRS.getObject(12);
        iAccepteddate = (java.sql.Timestamp)aRS.getObject(13);
        iTopprioritydate = (java.sql.Timestamp)aRS.getObject(14);
        iStartanalysisdate = (java.sql.Timestamp)aRS.getObject(15);
        iEndanalysisdate = (java.sql.Timestamp)aRS.getObject(16);
        iModificationdate = (java.sql.Timestamp)aRS.getObject(17);*/
        super(aRS);
    }

    /**
     * This methods reads all projects from the Project table that have a
     * calibrated differential standard deviation.
     *
     * @param aConn Connection to read the projects from.
     * @param aId Connection to read the projects from.
     * @return  Project with the project in the 'Project' table.
     * @throws SQLException when the retrieving of the project went wrong.
     */
    public static Project getProjectById(Connection aConn, Long aId) throws SQLException {
        PreparedStatement prep = aConn.prepareStatement("select * from project where projectid = ?");
        prep.setLong(1, aId);
        ResultSet rs = prep.executeQuery();
        Project lProject = null;
        while(rs.next()) {
            lProject = new Project(rs);
        }
        rs.close();
        prep.close();

        return lProject;
    }

    /**
     * This methods reads all projects from the Project table.
     *
     * @param aConn Connection to read the projects from.
     * @return  Project[] with the projects in the 'Project' table.
     * @throws SQLException when the retrieving of the projects went wrong.
     */
    public static Project[] getAllProjects(Connection aConn) throws SQLException {
        PreparedStatement prep = aConn.prepareStatement("select * from project");
        ResultSet rs = prep.executeQuery();
        Vector v = new Vector();
        while(rs.next()) {
            v.add(new Project(rs));
        }
        rs.close();
        prep.close();
        Project[] lProjects = new Project[v.size()];
        v.toArray(lProjects);

        return lProjects;
    }

    /**
     * This methods reads all projects from the Project table.
     *
     * @param aConn Connection to read the projects from.
     * @return  Project[] with the projects in the 'Project' table.
     * @throws SQLException when the retrieving of the projects went wrong.
     */
    public static Project[] getAllNewProjects(Connection aConn) throws SQLException {
        PreparedStatement prep = aConn.prepareStatement("select * from project where priority = -1");
        ResultSet rs = prep.executeQuery();
        Vector v = new Vector();
        while(rs.next()) {
            v.add(new Project(rs));
        }
        rs.close();
        prep.close();
        Project[] lProjects = new Project[v.size()];
        v.toArray(lProjects);

        return lProjects;
    }

    /**
     * This methods reads all projects from the Project table.
     *
     * @param aConn Connection to read the projects from.
     * @return  Project[] with the projects in the 'Project' table.
     * @throws SQLException when the retrieving of the projects went wrong.
     */
    public static Project[] getAllFinishedProjects(Connection aConn, long aInstrumentId) throws SQLException {
        PreparedStatement prep = aConn.prepareStatement("select * from project where l_instrumentid_accepted = ? and priority = 0 order by modificationdate desc");
        prep.setLong(1, aInstrumentId);
        ResultSet rs = prep.executeQuery();
        Vector v = new Vector();
        while(rs.next()) {
            v.add(new Project(rs));
        }
        rs.close();
        prep.close();
        Project[] lProjects = new Project[v.size()];
        v.toArray(lProjects);

        return lProjects;
    }

    /**
     * This methods gives all projects for a specific instrument ordered by priority.
     *
     * @param aConn Connection to read the projects from.
     * @param aInstrumentId Long with the instrumentid.
     * @return  Project[] with the projects in the 'Project' table.
     * @throws SQLException when the retrieving of the projects went wrong.
     */
    public static Project[] getAllUnfinishedProjectsForInstrument(Connection aConn, long aInstrumentId) throws SQLException {
        PreparedStatement prep = aConn.prepareStatement("select * from project where l_instrumentid_accepted = ? and priority > 0 order by priority");
        prep.setLong(1, aInstrumentId);
        ResultSet rs = prep.executeQuery();
        Vector v = new Vector();
        while(rs.next()) {
            v.add(new Project(rs));
        }
        rs.close();
        prep.close();
        Project[] lProjects = new Project[v.size()];
        v.toArray(lProjects);

        return lProjects;
    }

    /**
	 * This method allows the caller to update the data represented by this
	 * object in a persistent store.
	 *
	 * @param   aConn Connection to the persistent store.
	 */
	public int updateAcceptedProject(Connection aConn) throws SQLException {
		if(!this.iUpdated) {
			return 0;
		}
        int result;
        if(iPriority != 1){
            PreparedStatement lStat = aConn.prepareStatement("UPDATE project SET projectid = ?, name = ?, description = ?, l_userid = ?, l_instrumentid_proposed = ?, l_instrumentid_accepted = ?, priority = ?, username = ?, creationdate = ?, accepteddate = NOW(), topprioritydate = ?, startanalysisdate = ?, endanalysisdate = ?, modificationdate = CURRENT_TIMESTAMP WHERE projectid = ?");
            lStat.setLong(1, iProjectid);
            lStat.setObject(2, iName);
            lStat.setObject(3, iDescription);
            lStat.setLong(4, iL_userid);
            lStat.setLong(5, iL_instrumentid_proposed);
            lStat.setLong(6, iL_instrumentid_accepted);
            lStat.setLong(7, iPriority);
            lStat.setObject(8, iUsername);
            lStat.setObject(9, iCreationdate);
            lStat.setObject(10, iTopprioritydate);
            lStat.setObject(11, iStartanalysisdate);
            lStat.setObject(12, iEndanalysisdate);
            lStat.setLong(13, iProjectid);
            result = lStat.executeUpdate();
            lStat.close();
            this.iUpdated = false;

        } else {
            PreparedStatement lStat = aConn.prepareStatement("UPDATE project SET projectid = ?, name = ?, description = ?, l_userid = ?, l_instrumentid_proposed = ?, l_instrumentid_accepted = ?, priority = ?, username = ?, creationdate = ?, accepteddate = NOW(), topprioritydate = NOW(), startanalysisdate = ?, endanalysisdate = ?, modificationdate = CURRENT_TIMESTAMP WHERE projectid = ?");
            lStat.setLong(1, iProjectid);
            lStat.setObject(2, iName);
            lStat.setObject(3, iDescription);
            lStat.setLong(4, iL_userid);
            lStat.setLong(5, iL_instrumentid_proposed);
            lStat.setLong(6, iL_instrumentid_accepted);
            lStat.setLong(7, iPriority);
            lStat.setObject(8, iUsername);
            lStat.setObject(9, iCreationdate);
            lStat.setObject(10, iStartanalysisdate);
            lStat.setObject(11, iEndanalysisdate);
            lStat.setLong(12, iProjectid);
            result = lStat.executeUpdate();
            lStat.close();
            this.iUpdated = false;
        }
		return result;
	}

    /**
	 * This method allows the caller to update the data represented by this
	 * object in a persistent store.
	 *
	 * @param   aConn Connection to the persitent store.
     * @throws java.sql.SQLException
	 */
	public void updateProjectDone(Connection aConn) throws SQLException {
        PreparedStatement lStat = aConn.prepareStatement("UPDATE project SET projectid = ?, name = ?, description = ?, l_userid = ?, l_instrumentid_proposed = ?, l_instrumentid_accepted = ?, priority = ?, username = ?, creationdate = ?, accepteddate = ?, topprioritydate = ?, startanalysisdate = ?, endanalysisdate = NOW(), modificationdate = CURRENT_TIMESTAMP WHERE projectid = ?");
        lStat.setLong(1, iProjectid);
        lStat.setObject(2, iName);
        lStat.setObject(3, iDescription);
        lStat.setLong(4, iL_userid);
        lStat.setLong(5, iL_instrumentid_proposed);
        lStat.setLong(6, iL_instrumentid_accepted);
        lStat.setLong(7, iPriority);
        lStat.setObject(8, iUsername);
        lStat.setObject(9, iCreationdate);
        lStat.setObject(10, iAccepteddate);
        lStat.setObject(11, iTopprioritydate);
        lStat.setObject(12, iStartanalysisdate);
        lStat.setLong(13, iProjectid);
        lStat.executeUpdate();
        lStat.close();
        this.iUpdated = false;
	}

    /**
	 * This method allows the caller to update the data represented by this
	 * object in a persistent store.
	 *
	 * @param   aConn Connection to the persitent store.
	 */
	public int updatePriorityProject(Connection aConn) throws SQLException {
		if(!this.iUpdated) {
			return 0;
		}
        int result;
        if(iPriority != 1){
            PreparedStatement lStat = aConn.prepareStatement("UPDATE project SET projectid = ?, name = ?, description = ?, l_userid = ?, l_instrumentid_proposed = ?, l_instrumentid_accepted = ?, priority = ?, username = ?, creationdate = ?, accepteddate = ?, topprioritydate = ?, startanalysisdate = ?, endanalysisdate = ?, modificationdate = CURRENT_TIMESTAMP WHERE projectid = ?");
            lStat.setLong(1, iProjectid);
            lStat.setObject(2, iName);
            lStat.setObject(3, iDescription);
            lStat.setLong(4, iL_userid);
            lStat.setLong(5, iL_instrumentid_proposed);
            lStat.setLong(6, iL_instrumentid_accepted);
            lStat.setLong(7, iPriority);
            lStat.setObject(8, iUsername);
            lStat.setObject(9, iCreationdate);
            lStat.setObject(10, iAccepteddate);
            lStat.setObject(11, iTopprioritydate);
            lStat.setObject(12, iStartanalysisdate);
            lStat.setObject(13, iEndanalysisdate);
            lStat.setLong(14, iProjectid);
            result = lStat.executeUpdate();
            lStat.close();
            this.iUpdated = false;

        } else {
            PreparedStatement lStat = aConn.prepareStatement("UPDATE project SET projectid = ?, name = ?, description = ?, l_userid = ?, l_instrumentid_proposed = ?, l_instrumentid_accepted = ?, priority = ?, username = ?, creationdate = ?, accepteddate = ?, topprioritydate = NOW(), startanalysisdate = ?, endanalysisdate = ?, modificationdate = CURRENT_TIMESTAMP WHERE projectid = ?");
            lStat.setLong(1, iProjectid);
            lStat.setObject(2, iName);
            lStat.setObject(3, iDescription);
            lStat.setLong(4, iL_userid);
            lStat.setLong(5, iL_instrumentid_proposed);
            lStat.setLong(6, iL_instrumentid_accepted);
            lStat.setLong(7, iPriority);
            lStat.setObject(8, iUsername);
            lStat.setObject(9, iCreationdate);
            lStat.setObject(10, iAccepteddate);
            lStat.setObject(11, iStartanalysisdate);
            lStat.setObject(12, iEndanalysisdate);
            lStat.setLong(13, iProjectid);
            result = lStat.executeUpdate();
            lStat.close();
            this.iUpdated = false;
        }
		return result;
	}

    public void createNewProject(Connection aConn) throws SQLException{
        PreparedStatement lStat = aConn.prepareStatement("INSERT INTO project (name, description, l_userid ,l_labelid, l_taxonomyid, l_protocolid, l_instrumentid_proposed, priority, l_project_typeid, username, creationdate, modificationdate) values(?, ?, ?, ?,?,?,?, ?, ?,CURRENT_USER, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)");
        lStat.setString(1, iName);
        lStat.setString(2, iDescription);
        lStat.setLong(3, iL_userid);
        lStat.setLong(4, iL_labelid);
        lStat.setLong(5, iL_taxonomyid);
        lStat.setLong(6, iL_protocolid);
        lStat.setLong(7, iL_instrumentid_proposed);
        lStat.setLong(8, -1);
        lStat.setLong(9, iL_project_typeid);

        lStat.executeUpdate();
        ResultSet lrsKeys = lStat.getGeneratedKeys();
		ResultSetMetaData lrsmKeys = lrsKeys.getMetaData();
		int colCount = lrsmKeys.getColumnCount();
		iKeys = new Object[colCount];
		while(lrsKeys.next()) {
			for(int i=0;i<iKeys.length;i++) {
				iKeys[i] = lrsKeys.getObject(i+1);
			}
		}
        this.setProjectid(Long.valueOf(iKeys[0].toString()));
        lrsKeys.close();
		lStat.close();
    }

   
    /**
     * Returns a String representation for this Project.
     *
     * @return  String  with the String representation for this Project.
     */
    public String toString() {
        return this.iName;
    }

}